/**
 * 
 */
package com.xbang.attendance.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.hql.ast.tree.FromClause;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.xbang.attendance.entity.order.DeliveryDetails;
import com.xbang.attendance.entity.order.DeliveryOrder;
import com.xbang.attendance.entity.order.PurchaseOrder;
import com.xbang.attendance.entity.order.StockOutLog;
import com.xbang.attendance.entity.product.Product;
import com.xbang.attendance.entity.stock.Inventory;
import com.xbang.attendance.entity.stock.InventoryExchange;
import com.xbang.attendance.entity.stock.InventoryRecord;
import com.xbang.attendance.entity.stock.InventoryWarningRecord;
import com.xbang.attendance.entity.system.EntityWarning;
import com.xbang.attendance.entity.system.Warehouse;
import com.xbang.attendance.entity.system.WarehouseProductAgreement;
import com.xbang.attendance.security.support.SecurityUserHolder;
import com.xbang.attendance.service.InventoryService;
import com.xbang.attendance.utils.DateUtil;
import com.xbang.attendance.utils.FieldUtil;
import com.xbang.core.dao.search.component.SearchComponent;
import com.xbang.core.dao.support.GenericDaoSupport;
import com.xbang.core.service.impl.BaseServiceImpl;
import com.xbang.core.utils.DateUtils;

import enumeration.InventoryOperation;
import enumeration.Temp;
import enumeration.WarningType;

/**
 * @author dev.zhang
 * 
 */
@Service("inventoryService")
public class InventoryServiceImpl extends BaseServiceImpl implements InventoryService {

	@Autowired
	private GenericDaoSupport genericDaoSupport;

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.InventoryService#getInventoryByParameters(
	 * com.xbang.attendance.entity.system.Warehouse,
	 * com.xbang.attendance.entity.product.Product)
	 */
	public Inventory getInventoryByParameters(Warehouse warehouse, Product product) throws Exception {

		String sentence = " FROM Inventory inventory WHERE inventory.warehouse = :warehouse AND inventory.product = :product";

		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("warehouse", warehouse);
		parameters.put("product", product);

		List objects = genericDaoSupport.searchForList(sentence, parameters);

		if (null != objects && !objects.isEmpty()) {
			return (Inventory) objects.get(0);
		}

		return null;
	}

	@Override
	public StockOutLog getLog(PurchaseOrder po,  Product pro)
			throws Exception {
		String sentence = " FROM StockOutLog log WHERE log.purchaseOrder = :purchaseOrder AND log.product = :product";

		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("purchaseOrder", po);
	//	parameters.put("deliveryOrder", de);
		parameters.put("product", pro);

		List objects = genericDaoSupport.searchForList(sentence, parameters);

		if (null != objects && !objects.isEmpty()) {
			return (StockOutLog) objects.get(0);
		}
		return null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.InventoryService#getInventoryExchangeByKey
	 * (java.lang.Integer)
	 */
	public InventoryExchange getInventoryExchangeByKey(Integer id) throws Exception {
		Object object = genericDaoSupport.get(InventoryExchange.class, id);
		return object == null ? null : (InventoryExchange) object;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.InventoryService#getInventoryRecordByKey(java
	 * .lang.Integer)
	 */
	public InventoryRecord getInventoryRecordByKey(Integer id) throws Exception {
		Object object = genericDaoSupport.get(InventoryRecord.class, id);
		return object == null ? null : (InventoryRecord) object;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.InventoryService#getInventoryByWarehouse(com
	 * .xbang.attendance.entity.system.Warehouse)
	 */
	public List<Inventory> getInventoryByWarehouse(Warehouse warehouse) throws Exception {
		String hqlSentence = "FROM Inventory inventory WHERE inventory.warehouse.id = :warehouse";

		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("warehouse", warehouse.getId());

		return genericDaoSupport.searchForList(hqlSentence, parameters);

	}

	@Override
	public Boolean warehouseIsExist(Warehouse warehouse) throws Exception {
		List<Inventory> inventories = getInventoryByWarehouse(warehouse);
		return !inventories.isEmpty();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.InventoryService#getInventoryByProduct(com
	 * .xbang.attendance.entity.product.Product)
	 */
	@Override
	public Inventory getInventoryByProduct(Product product) throws Exception {
		String sentence = " FROM Inventory inventory WHERE inventory.product = :product";

		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("product", product);

		List objects = genericDaoSupport.searchForList(sentence, parameters);

		if (null != objects && !objects.isEmpty()) {
			return (Inventory) objects.get(0);
		}

		return null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.InventoryService#getInventoryByWarehouseId
	 * (java.lang.Integer)
	 */
	@Override
	public List<Inventory> getInventoryByWarehouseId(Integer id) throws Exception {
		return getInventoryByWarehouse((Warehouse) get(Warehouse.class, id));
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.InventoryService#searchInventoryRecords(com
	 * .xbang.attendance.entity.system.Warehouse,
	 * com.xbang.attendance.entity.product.Product,
	 * enumeration.InventoryOperation, boolean)
	 */
	public List<InventoryRecord> searchInventoryRecords(Warehouse warehouse, Product product, InventoryOperation inventoryOperation, boolean exactMatch)
			throws Exception {
		List<InventoryRecord> inventoryRecords = new ArrayList<InventoryRecord>();
		SearchComponent searchComponent = new SearchComponent();
		searchComponent.addSearchElement("eq.inventoryrecord.warehouse.id", warehouse.getId());
		searchComponent.addSearchElement("eq.inventoryrecord.product.id", product.getId());
		searchComponent.addSearchElement("eq.inventoryrecord.lastInventoryOperation", inventoryOperation);
		searchComponent.addSearchElement("eq.inventoryrecord.exactMatch", exactMatch);

		String hqlSentence = "FROM InventoryRecord inventoryrecord WHERE 1 = 1 ";

		inventoryRecords = genericDaoSupport.searchForList(searchComponent.getSearchSentence(hqlSentence, true), searchComponent.getParameters());

		return inventoryRecords;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.InventoryService#searchInventoryRecords(com
	 * .xbang.attendance.entity.system.Warehouse,
	 * com.xbang.attendance.entity.product.Product,
	 * enumeration.InventoryOperation)
	 */
	public List<InventoryRecord> searchInventoryRecords(Warehouse warehouse, Product product, InventoryOperation inventoryOperation) throws Exception {
		return searchInventoryRecords(warehouse, product, inventoryOperation, false);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.InventoryService#searchInventoryRecords(com
	 * .xbang.attendance.entity.system.Warehouse,
	 * com.xbang.attendance.entity.product.Product)
	 */
	public List<InventoryRecord> searchInventoryRecords(Warehouse warehouse, Product product) throws Exception {
		return searchInventoryRecords(warehouse, product, InventoryOperation.OUT, false);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @seecom.xbang.attendance.service.InventoryService#
	 * updateInventoryExchangeAgreementAmount(com.xbang.attendance.entity.
	 * stock.Inventory)
	 */
	public void updateInventoryExchangeAgreementAmount(Inventory inventory) throws Exception {

		SearchComponent searchComponent = new SearchComponent();
		searchComponent.addSearchElement("eq.warehouseproductagreement.warehouse.id", inventory.getWarehouse().getId());
		searchComponent.addSearchElement("eq.warehouseproductagreement.product.id", inventory.getProduct().getId());

		String hqlSentence = "FROM WarehouseProductAgreement warehouseproductagreement WHERE 1 = 1";

		List<WarehouseProductAgreement> warehouseProductAgreements = searchForList(hqlSentence, searchComponent);

		if (warehouseProductAgreements != null && !warehouseProductAgreements.isEmpty()) {
			WarehouseProductAgreement warehouseProductAgreement = warehouseProductAgreements.get(0);
			inventory.setAgreementAmount(warehouseProductAgreement.getNumber());
			if (inventory.getAmount() != null && inventory.getAgreementAmount() != null) {
				// 约定数量等于现有数量-更新调货信息=0
				if (inventory.getAgreementAmount().compareTo(inventory.getAmount()) <= 0) {
					inventory.setExchangeInventory(new BigDecimal(0));
				}
			}
			genericDaoSupport.update(inventory);
		} else {
			inventory.setAgreementAmount(new BigDecimal(0));
			genericDaoSupport.update(inventory);
		}

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.InventoryService#updateInventoryWarning(com
	 * .xbang.attendance.entity.stock.Inventory)
	 */
	public void updateInventoryWarning(Inventory inventory) throws Exception {

		String hqlSentence = "FROM EntityWarning entitywarning WHERE 1 = 1 ";
		SearchComponent searchComponent = new SearchComponent();
		searchComponent.addSearchElement("eq.entitywarning.product.id", inventory.getProduct().getId());
		searchComponent.addSearchElement("eq.entitywarning.warehouse.id", inventory.getWarehouse().getId());
		List<EntityWarning> entityWarnings = genericDaoSupport.searchForList(searchComponent.getSearchSentence(hqlSentence, true), searchComponent
				.getParameters());

		for (EntityWarning entityWarning : entityWarnings) {
			Warehouse warehouse = entityWarning.getWarehouse();
			Product product = entityWarning.getProduct();
			// //
			if (entityWarning.getWarningType().compareTo(WarningType.constants) == 0) {
				// 常量
				BigDecimal constantsAmount = entityWarning.getNumber();
				if (constantsAmount == null) {
					return;
				}
				if (inventory.getAmount() == null) {
					inventory.setAmount(new BigDecimal(0));
				}
				if (entityWarning.entityWarningEqules(constantsAmount, entityWarning.getWarningEqualesType(), inventory.getAmount())) {
					// /增加预警信息

					InventoryWarningRecord inventoryWarningRecord = new InventoryWarningRecord().initInventoryWarningRecord(entityWarning, warehouse, product,
							inventory);
					inventoryWarningRecord.setInventory(inventory);
					genericDaoSupport.save(inventoryWarningRecord);
					inventory.setInventoryWarningRecord(inventoryWarningRecord);
					genericDaoSupport.update(inventory);
				} else {
					inventory.setInventoryWarningRecord(null);
					genericDaoSupport.update(inventory);
				}
			} else {
				// 百分比
				BigDecimal constantsAmount = entityWarning.getNumber();
				BigDecimal percentage = entityWarning.getPercentage();
				if (percentage == null || constantsAmount == null) {
					return;
				}
				if (inventory.getAmount() == null) {
					inventory.setAmount(new BigDecimal(0));
				}
				if (entityWarning.entityWarningEqules(constantsAmount, percentage, entityWarning.getWarningEqualesType(), inventory.getAmount())) {
					// /增加预警信息
					InventoryWarningRecord inventoryWarningRecord = new InventoryWarningRecord().initInventoryWarningRecord(entityWarning, warehouse, product,
							inventory);
					genericDaoSupport.save(inventoryWarningRecord);
					inventory.setInventoryWarningRecord(inventoryWarningRecord);
					genericDaoSupport.update(inventory);
				} else {
					inventory.setInventoryWarningRecord(null);
					genericDaoSupport.update(inventory);
				}
			}

		}

	}

	/**
	 * @param warningInventorys
	 * @throws Exception
	 */
	public void updateInventoryWarning(Set<Inventory> warningInventorys) throws Exception {
		List<Inventory> inventorys = genericDaoSupport.searchForList("FROM Inventory inventory WHERE 1 = 1 ", new HashMap<String, Object>());
		if (inventorys != null && warningInventorys != null && !inventorys.isEmpty()) {
			for (Inventory inventory : inventorys) {
				boolean isExists = false;
				for (Iterator<Inventory> inventoryIterator = warningInventorys.iterator(); inventoryIterator.hasNext();) {
					Inventory tmpInventory = inventoryIterator.next();
					if (inventory.getId().equals(tmpInventory.getId())) {
						isExists = true;
						break;
					}
				}
				if (!isExists) {
					inventory.setInventoryWarningRecord(null);
					genericDaoSupport.update(inventory);
				}
			}
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @seecom.xbang.attendance.service.InventoryService#
	 * updateInventoryRecordByReplenishment(com.xbang.attendance.entity.system
	 * .Warehouse, com.xbang.attendance.entity.product.Product,
	 * com.xbang.attendance.entity.stock.Inventory, java.lang.String,
	 * java.util.Date, java.math.BigDecimal)
	 */
	public void updateInventoryRecordByReplenishment(Warehouse warehouse, Product product, Inventory inventory, String soNumber, Date soDate,
			BigDecimal replenishmentAmount) throws Exception {

		Date inventoryRecordDate = new Date();

		InventoryRecord reInventoryRecord = getRecentReplenishmentRecord(warehouse, product);
		if (reInventoryRecord == null) {
			// 插入记录
			InventoryRecord newInventoryRecord = new InventoryRecord().initialInventoryRecord(warehouse, product, InventoryOperation.REPLENISHMENT,
					replenishmentAmount, SecurityUserHolder.getCurrentUser().getName());
			newInventoryRecord.setPoNumber(soNumber);
			newInventoryRecord.setSalesOrderDate(soDate);
			newInventoryRecord.setInventory(inventory);
			genericDaoSupport.save(newInventoryRecord);
		} else {
			// 拿到原有的入库记录
			List<InventoryRecord> inventoryRecords = getBeforTimeInventoryRecord(warehouse, product, reInventoryRecord.getOperationDate());
			BigDecimal tmpAmount = replenishmentAmount == null ? new BigDecimal(0) : replenishmentAmount;
			if (inventoryRecords != null && !inventoryRecords.isEmpty()) {
				for (InventoryRecord inventoryRecord : inventoryRecords) {
					if (tmpAmount.compareTo(new BigDecimal(0)) > 0) {
						if (inventoryRecord.getMatchAmount() == null) {
							inventoryRecord.setMatchAmount(new BigDecimal(0));
						}
						BigDecimal outAmount = inventoryRecord.getAmount().subtract(inventoryRecord.getMatchAmount());

						if (outAmount.compareTo(tmpAmount) < 0) {
							// 补货记录大于最近的记录
							tmpAmount = tmpAmount.subtract(outAmount);

							inventoryRecord.setMatchAmount(inventoryRecord.getAmount());
							inventoryRecord.setExactMatch(true);
							genericDaoSupport.update(inventoryRecord);
							insertNewReplenishmentInventoryRecord(inventory, warehouse, product, soNumber, soDate, inventoryRecordDate, inventoryRecord,
									outAmount);

						} else if (outAmount.compareTo(tmpAmount) == 0) {
							// 补货记录等于最近的记录
							inventoryRecord.setMatchAmount(inventoryRecord.getAmount());
							inventoryRecord.setExactMatch(true);
							genericDaoSupport.update(inventoryRecord);

							// /插入补货记录

							insertNewReplenishmentInventoryRecord(inventory, warehouse, product, soNumber, soDate, inventoryRecordDate, inventoryRecord,
									outAmount);

							tmpAmount = tmpAmount.subtract(outAmount);

						} else {
							// 补货记录小于最近的记录
							inventoryRecord.setMatchAmount(inventoryRecord.getMatchAmount().add(tmpAmount));
							inventoryRecord.setExactMatch(false);
							genericDaoSupport.update(inventoryRecord);

							// /插入补货记录

							insertNewReplenishmentInventoryRecord(inventory, warehouse, product, soNumber, soDate, inventoryRecordDate, inventoryRecord,
									outAmount);

							tmpAmount = new BigDecimal(0);
						}
					}
				}
			}
		}

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.InventoryService#getRecentReplenishmentRecord
	 * (com.xbang.attendance.entity.stock.Inventory )
	 */
	public InventoryRecord getRecentReplenishmentRecord(Inventory inventory) throws Exception {
		return getRecentReplenishmentRecord(inventory.getWarehouse(), inventory.getProduct());
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.InventoryService#getRecentReplenishmentRecord
	 * (com.xbang.attendance.entity.system.Warehouse ,
	 * com.xbang.attendance.entity.product.Product)
	 */
	public InventoryRecord getRecentReplenishmentRecord(Warehouse warehouse, Product product) throws Exception {

		String hqlSentence = "FROM InventoryRecord inventoryrecord WHERE 1 = 1";

		SearchComponent searchComponent = new SearchComponent();
		searchComponent.addSearchElement("eq.inventoryrecord.lastInventoryOperation", InventoryOperation.REPLENISHMENT);
		searchComponent.addSearchElement("orderBy.inventoryrecord.operationDate", "DESC");
		searchComponent.addSearchElement("eq.inventoryrecord.warehouse.id", warehouse.getId());
		searchComponent.addSearchElement("eq.inventoryrecord.product.id", product.getId());

		List<InventoryRecord> inventoryRecords = genericDaoSupport.searchForList(searchComponent.getSearchSentence(hqlSentence, true), searchComponent
				.getParameters(), 0, 2);

		return inventoryRecords == null || inventoryRecords.isEmpty() ? null : inventoryRecords.get(0);
	}

	public List<InventoryRecord> getBeforTimeInventoryRecord(Warehouse warehouse, Product product, Date date) throws Exception {

		String hqlSentence = "FROM InventoryRecord inventoryrecord WHERE 1 = 1";

		Set<InventoryOperation> inventoryOperations = new HashSet<InventoryOperation>();
		inventoryOperations.add(InventoryOperation.OUT);
		inventoryOperations.add(InventoryOperation.TURE_OUT);
		SearchComponent searchComponent = new SearchComponent();
		searchComponent.addSearchElement("eq.inventoryrecord.lastInventoryOperation", InventoryOperation.OUT);
		searchComponent.addSearchElement("geq.inventoryrecord.operationDate", date);
		searchComponent.addSearchElement("eq.inventoryrecord.warehouse.id", warehouse.getId());
		searchComponent.addSearchElement("eq.inventoryrecord.product.id", product.getId());

		return genericDaoSupport.searchForList(searchComponent.getSearchSentence(hqlSentence, true), searchComponent.getParameters());
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @seecom.xbang.attendance.service.InventoryService#
	 * getWarehouseProductAgreementByParameter(com.xbang.attendance.entity
	 * .system.Warehouse, com.xbang.attendance.entity.product.Product)
	 */
	public WarehouseProductAgreement getWarehouseProductAgreementByParameter(Warehouse warehouse, Product product) throws Exception {

		WarehouseProductAgreement warehouseProductAgreement = null;

		SearchComponent searchComponent = new SearchComponent();
		searchComponent.addSearchElement("eq.warehouseproductagreement.warehouse.id", warehouse.getId());
		searchComponent.addSearchElement("eq.warehouseproductagreement.product.id", product.getId());

		String hqlSentence = "FROM WarehouseProductAgreement warehouseproductagreement WHERE 1 = 1";

		List<WarehouseProductAgreement> warehouseProductAgreements = searchForList(hqlSentence, searchComponent);

		if (warehouseProductAgreements != null && !warehouseProductAgreements.isEmpty()) {
			warehouseProductAgreement = warehouseProductAgreements.get(0);
		}
		return warehouseProductAgreement;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.InventoryService#getInventoryRecordTimePart
	 * (com.xbang.attendance.entity.system.Warehouse )
	 */
	@Override
	public List<String> getInventoryRecordTimePart(Warehouse warehouse) throws Exception {

		List<String> timeparts = new ArrayList<String>();

		String hqlSentence = "FROM InventoryRecord inventoryRecord WHERE 1 = 1 ";
		SearchComponent searchComponent = new SearchComponent();
		searchComponent.addSearchElement("eq.inventoryRecord.lastInventoryOperation", InventoryOperation.REPLENISHMENT);
		searchComponent.addSearchElement("eq.inventoryrecord.warehouse.id", warehouse.getId());
		searchComponent.addSearchElement("orderby.inventoryRecord.operationDate", "DESC");
		List<InventoryRecord> inventoryRecords = searchForList(hqlSentence, searchComponent);

		if (inventoryRecords != null && !inventoryRecords.isEmpty()) {
			for (InventoryRecord inventoryRecord : inventoryRecords) {
				Date tmpDate = inventoryRecord.getOperationDate();

				int year = DateUtils.getYear(tmpDate);
				int month = DateUtils.getMonth(tmpDate);

				Date newDate = DateUtils.parseDate("" + year + "-" + month + "25", "yyyy-MM-dd");
				if (tmpDate.after(newDate)) {
					if (!timeparts.contains("" + year + "-" + month)) {
						timeparts.add("" + year + "-" + month);
					}
				} else {
					newDate = DateUtils.addDateEnum(tmpDate, Calendar.MONTH, -1);
					month = DateUtils.getMonth(newDate);
					if (!timeparts.contains("" + year + "-" + month)) {
						timeparts.add("" + year + "-" + month);
					}
				}
			}
		}

		return timeparts;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.InventoryService#getInveotyrRecordTimePart()
	 */
	public List<String> getInventoryRecordTimePart() throws Exception {

		int firstYear = DateUtils.getYear(new Date()), firstMonth = DateUtils.getMonth(new Date());
		boolean isFirst = true;

		List<String> timeparts = new ArrayList<String>();

		String hqlSentence = "FROM InventoryRecord inventoryRecord WHERE 1 = 1 ";
		SearchComponent searchComponent = new SearchComponent();
		searchComponent.addSearchElement("eq.inventoryRecord.lastInventoryOperation", InventoryOperation.REPLENISHMENT);
		searchComponent.addSearchElement("orderby.inventoryRecord.operationDate", "DESC");
		List<InventoryRecord> inventoryRecords = searchForList(hqlSentence, searchComponent);

		if (inventoryRecords != null && !inventoryRecords.isEmpty()) {
			for (InventoryRecord inventoryRecord : inventoryRecords) {

				Date tmpDate = inventoryRecord.getOperationDate();

				int year = DateUtils.getYear(tmpDate);
				int month = DateUtils.getMonth(tmpDate);
				if (isFirst) {
					firstYear = year;
					firstMonth = month;
					isFirst = false;
				}
				Date newDate = DateUtils.parseDate("" + year + "-" + month + "-25", "yyyy-MM-dd");
				if (tmpDate.after(newDate)) {
					if (!timeparts.contains("" + year + "-" + month)) {
						timeparts.add("" + year + "-" + month);
					}
				} else {
					newDate = DateUtils.addDateEnum(tmpDate, -1, Calendar.MONTH);
					month = DateUtils.getMonth(newDate);
					if (!timeparts.contains("" + year + "-" + month)) {
						timeparts.add("" + year + "-" + month);
					}
				}
			}
		}

		int nowYear = DateUtils.getYear(new Date());
		int nowMonth = DateUtils.getMonth(new Date());
		for (int yearIndex = firstYear; yearIndex <= nowYear; yearIndex++) {
			for (int monthIndex = 1; monthIndex <= 12; monthIndex++) {
				if ((yearIndex < nowYear) || (yearIndex == nowYear && monthIndex <= nowMonth)) {
					String tmpStr = "" + yearIndex + "-" + monthIndex;
					if (!timeparts.contains(tmpStr)) {
						timeparts.add(tmpStr);
					}
				}
			}
		}

		return timeparts;
	}

	/**
	 * 插入一条补货记录
	 * 
	 * @param warehouse
	 * @param product
	 * @param soNumber
	 * @param soDate
	 * @param inventoryRecordDate
	 * @param inventoryRecord
	 * @param amount
	 */
	private void insertNewReplenishmentInventoryRecord(Inventory inventory, Warehouse warehouse, Product product, String soNumber, Date soDate,
			Date inventoryRecordDate, InventoryRecord inventoryRecord, BigDecimal amount) {
		InventoryRecord newInventoryRecord = new InventoryRecord().initialInventoryRecord(warehouse, product, InventoryOperation.REPLENISHMENT, amount,
				SecurityUserHolder.getCurrentUser().getName());
		newInventoryRecord.setPoNumber(soNumber);
		newInventoryRecord.setSalesOrderDate(soDate);
		newInventoryRecord.setOperationDate(inventoryRecordDate);
		newInventoryRecord.setDeliveryOrder(inventoryRecord.getDeliveryOrder());
		newInventoryRecord.setDeliveryDetails(inventoryRecord.getDeliveryDetails());
		newInventoryRecord.setInventory(inventory);

		genericDaoSupport.save(newInventoryRecord);
	}
	/*
	 * (non-Javadoc)
	 * @see com.xbang.attendance.service.InventoryService#getInventoryRecordsByPurchaseOrder(com.xbang.attendance.entity.order.PurchaseOrder)
	 */
	@Override
	public List<InventoryRecord> getInventoryRecordsByPurchaseOrder(
			PurchaseOrder order) {
		String hql = "FROM InventoryRecord inv WHERE inv.lastInventoryOperation = '"+InventoryOperation.OUT 
			+"' OR inv.lastInventoryOperation = '"+InventoryOperation.TRANSFERRED+"' AND inv.purchaseOrder.id = :id";
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("id", order.getId());
		
		List objects = genericDaoSupport.searchForList(hql, parameters);
		
		return objects;
	}

	@Override
	public Boolean isAddProduct(Integer pid) throws Exception {
		StringBuilder sbBuilder = new StringBuilder("SELECT COUNT(1) FROM inventory_record inventoryr0_ WHERE (inventoryr0_.last_inventory_operation='OUT' OR inventoryr0_.last_inventory_operation='TRANSFERRED') AND inventoryr0_.is_add_product = 'N' AND inventoryr0_.purchase_order_id = ?");
		int count =	genericDaoSupport.getJdbcTemplate().queryForInt(sbBuilder.toString(),pid);
		return count == 0 ? true : false;
	}

	@Override
	public void addProduct(List<InventoryRecord> inventoryRecords,String soNumber)
			throws Exception {
		for (InventoryRecord inventoryRecord : inventoryRecords) {
			
			if(!inventoryRecord.isAddProduct()){
				Warehouse warehouse =inventoryRecord.getWarehouse();
				//如果是借的货,就补给借的人.
				if(inventoryRecord.getLastInventoryOperation().equals(InventoryOperation.TRANSFERRED))
				{
					warehouse = inventoryRecord.getWare();
				}	
				
				Inventory inventory =inventoryRecord.getInventory(); //拿到库存
				inventory.setAmount(inventory.getAmount().add(inventoryRecord.getAmount()));

				inventory.setOutAmount(inventory.getOutAmount().subtract(inventoryRecord.getAmount()));
				
				inventory.updateOperation(SecurityUserHolder.getCurrentUser().getUsername());
				
				inventoryRecord.setPoNumber(soNumber);
				inventoryRecord.setAddProduct(true);
				
				save(inventory);
				inventoryRecord.setAddProductDate(new Date());
				// 保存调入记录
				save(inventoryRecord);
				
				//补货记录
				InventoryRecord record = new InventoryRecord().initialInventoryRecord(warehouse, inventoryRecord.getProduct(), InventoryOperation.REPLENISHMENT,
						inventoryRecord.getAmount(), SecurityUserHolder.getCurrentUser().getUsername());
				
				record.setAddProductDate(new Date());
				record.setInventory(inventory);
				record.setPoNumber(soNumber);
				record.setDeliveryOrder(inventoryRecord.getDeliveryOrder());
				record.setDeliveryDetails(inventoryRecord.getDeliveryDetails());
				record.setPurchaseOrder(inventoryRecord.getPurchaseOrder());
				
				save(record);
				//更新借货人的调出记录,如补货状态之类的
				if(inventoryRecord.getLastInventoryOperation().equals(InventoryOperation.TRANSFERRED)){
					//String hql = 
					InventoryRecord record2 = getInventoryRecordByCondition(inventoryRecord.getPurchaseOrder(), inventoryRecord.getDeliveryOrder(),
							inventoryRecord.getDeliveryDetails(), inventory, warehouse);
					record2.setAddProduct(true);
					record2.setAddProductDate(new Date());
					save(record2);
				}
			}
		}
		
	}
	private InventoryRecord getInventoryRecordByCondition(PurchaseOrder purchaseOrder,DeliveryOrder deliveryOrder,DeliveryDetails deliveryDetails,Inventory inventory,Warehouse warehouse){
		InventoryRecord inventoryRecord = null;
		String hql ="FROM InventoryRecord inventoryRecord where inventoryRecord.purchaseOrder.id = ? and inventoryRecord.deliveryOrder.id = ?"
			.concat(" and inventoryRecord.deliveryDetails.id = ? and inventoryRecord.inventory.id = ? and inventoryRecord.warehouse.id = ? and inventoryRecord.lastInventoryOperation = ?");
		List<InventoryRecord> inventoryRecords =genericDaoSupport.getHibernateTemplate().find(hql,new Object[]{purchaseOrder.getId(),deliveryOrder.getId(),
				deliveryDetails.getId(),inventory.getId(),warehouse.getId(),InventoryOperation.TURE_OUT});
		return inventoryRecord != null ? null : inventoryRecords.get(0);
	}
	
	
	public String genericExcelFile(String webPath,String type) throws Exception {
		String genericExcelFile = "inventroy.xls";
		File file = new File(webPath + "//" + genericExcelFile);
		Map<Integer,List> map = getRegionAndWarehouse();
		HSSFWorkbook workbook = new HSSFWorkbook();
		
	//	int count=0;
		for (Map.Entry<Integer, List> entry : map.entrySet()) {
			List<Map> list = entry.getValue();
			
			HSSFSheet sheet = workbook.createSheet(list.get(0).get("rname").toString());
			List amounts = getAmounts(Integer.valueOf(list.get(0).get("rid").toString()));
			//sheet.protectSheet(password);
			int r = 0; // row index ;
			int c = 0; // cell index;
			HSSFRow row = sheet.createRow(r++);// 创建第一行
			
			HSSFFont cnFont = workbook.createFont();  
			cnFont.setFontHeightInPoints((short) 10);  
			cnFont.setFontName("宋体");
			
			HSSFCellStyle style  =  workbook.createCellStyle();
			   style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			   style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			   style.setBorderTop(HSSFCellStyle.BORDER_NONE);
			   style.setBorderRight(HSSFCellStyle.BORDER_THIN);
			   style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中  
			   style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
			  
			   style.setFont(cnFont);
			
			
			Map<Integer, Integer> cellIndexs = new HashMap<Integer, Integer>();
			row.setHeight((short)1000);
			HSSFCellStyle style2  =  workbook.createCellStyle();
			style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			style2.setBorderTop(HSSFCellStyle.BORDER_NONE);
			style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中  
			style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
			
			HSSFCell cell = row.createCell(c++);
			cell.setCellValue("SGM SAP");
			cell.setCellStyle(style2);
			HSSFCell ce5 = row.createCell(c++);
			ce5.setCellValue("新劲产品中文名称");
			ce5.setCellStyle(style2);
			for (Map m : list) {
				int index = c++;
				HSSFCell cell2 = row.createCell(index);
				cellIndexs.put(Integer.valueOf(m.get("wid").toString()),index);
				cell2.setCellValue(m.get("wname").toString());
				cell2.setCellStyle(style2);
			}
			
			
			Map<Integer,Integer> rowIndexs = new HashMap<Integer, Integer>();
			List products = getProducts1();
			for (Object object : products) {
				int index = r++;
				Map tmMap = (Map) object;

				HSSFRow row2 = sheet.createRow(index);//
				HSSFCell c1 = row2.createCell(0);
				c1.setCellStyle(style);
				
				c1.setCellValue(tmMap.get("product_code").toString());	
				HSSFCell c2 = row2.createCell(1);
				c2.setCellValue(tmMap.get("product_name").toString());
				c2.setCellStyle(style);
				rowIndexs.put(Integer.valueOf(tmMap.get("id").toString()),index);
			}
	
			for (Object object : amounts) {
				Map tmMap =(Map)object;
				Integer pid = Integer.valueOf(tmMap.get("product_id").toString());
				Integer wid = Integer.valueOf(tmMap.get("warehouse_id").toString());
	
				HSSFRow tr = sheet.getRow(rowIndexs.get(pid));
				HSSFCell tCell = tr.createCell(cellIndexs.get(wid));
				//if(type.equals("real"))
				//	tCell.setCellValue(tmMap.get("number").toString());
				///else {
					tCell.setCellValue(tmMap.get("amount").toString());
				//}
				tCell.setCellStyle(style);
				
				
			}
			sheet.autoSizeColumn(( short ) 0 ); // 调整第一列宽度 
	        sheet.autoSizeColumn(( short ) 1 ); // 调整第二列宽度 
		}
		OutputStream os = new FileOutputStream(file);
		workbook.write(os);// 写入
		InputStream in = new FileInputStream(file);
		os.close();
		in.close();
		return genericExcelFile;
	}
	
	private Map getRegionAndWarehouse(){
		StringBuilder sbBuilder = new StringBuilder("SELECT re.id AS rid,re.name" +
				" AS rname,war.id AS wid , war.code AS wcode,war.name AS wname " +
				"FROM inventory wpa LEFT JOIN " +
				"warehouse war ON wpa.warehouse_id " +
				"= war.id LEFT JOIN region re ON war.region = re.id GROUP BY re.name,war.code");
		logger.info(sbBuilder.toString());
		//return genericDaoSupport.queryForList(sBuilder.toString(),warehouseId);
		List list= genericDaoSupport.getJdbcTemplate().queryForList(sbBuilder.toString());
		Map<Integer,List> map = new HashMap<Integer,List>();
		
		for (Object object : list) {
			Map map2= (Map) object;
			if(map.containsKey(map2.get("rid"))){
				map.get(map2.get("rid")).add(map2);
			}
			else
			{
				List<Map> list2 = new ArrayList<Map>();
				list2.add(map2);
				map.put((Integer) map2.get("rid"),list2);
			}
		}
		return map; 
	}
	private  List<Map<String, Object>> getProducts1(){
		StringBuilder sBuilder = new StringBuilder("SELECT pro.id,pro.product_code,pro.product_name FROM inventory wpa LEFT JOIN product pro ON wpa.product_id = pro.id GROUP BY pro.product_code ORDER BY pro.product_code");
		return genericDaoSupport.getJdbcTemplate().queryForList(sBuilder.toString());
	}

	private List<Map<String,Object>> getAmounts(Integer region){
		StringBuilder sbBuilder = new StringBuilder("SELECT amount,product_id,warehouse_id,real_amount FROM inventory wpa")
		.append(" LEFT JOIN product pro    ON wpa.product_id = pro.id")
		.append(" LEFT JOIN warehouse war ON war.id = wpa.warehouse_id")
		.append(" WHERE war.region = ? ")
		.append("GROUP BY wpa.warehouse_id,wpa.product_id ORDER BY warehouse_id,pro.product_code");
		logger.info(sbBuilder.toString());
		return genericDaoSupport.getJdbcTemplate().queryForList(sbBuilder.toString(),region);
	}

	@Override
	public String genericExcelFile(String webPath,Date beginDate,Date endD) throws Exception {
		String genericExcelFile = "stock-".concat(DateUtils.format(new Date(), "yyyyMMddhhmmss")).concat(".xls");
		File file = new File(webPath + "//" + genericExcelFile);
		
		
		
		List<StockOutLog>  orderList = getData( beginDate, endD);
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("sheet1");
		sheet.setDefaultColumnWidth((short) 20);
		HSSFRow row = sheet.createRow(0);// 创建第一行
		int i = 0;
		for (FieldUtil.Filed field : FieldUtil.getStockOutLogFields()) {
			HSSFCell cell = row.createCell(i);
			cell.setCellValue(field.getName());
			i++;
		}
		if(orderList!=null){
			int rowIndex = 1;
			for(StockOutLog st :orderList){
				row = sheet.createRow(rowIndex++);
				HSSFCell cell = row.createCell(rowIndex);
				i = 0;
				cell = row.createCell(i++);
				cell.setCellValue(st.getPurchaseOrder().getOrderNumber());
				cell =row .createCell(i++);
				cell.setCellValue(st.getDeliveryOrder() != null ? st.getDeliveryOrder().getNumber() : "");
				cell =row .createCell(i++);
				cell.setCellValue(st.getWarehouse().getCode());
				cell =row .createCell(i++);
				cell.setCellValue(st.getWarehouse().getName());
				cell =row .createCell(i++);
				cell.setCellValue(st.getProduct().getProductCode());
				cell =row .createCell(i++);
				cell.setCellValue(st.getProduct().getProductName());
				cell =row .createCell(i++);
				cell.setCellValue(DateUtil.formatDate(st.getDate(), DateUtil.DATE_FORMAT));
				cell =row .createCell(i++);
				cell.setCellValue(st.getNumber().doubleValue());
			}
		}
		OutputStream os = new FileOutputStream(file);
		workbook.write(os);// 写入
		InputStream in = new FileInputStream(file);
		os.close();
		in.close();
	
	return genericExcelFile;
	}
	
	private List<StockOutLog> getData(Date beginDate,Date endDate)throws Exception{
		String hql= "FROM StockOutLog log  where  log.date <= :endDate AND log.date >=  :beginDate";
		Map parameters = new HashMap();
		parameters.put("endDate", endDate);
		parameters.put("beginDate", beginDate);

		List objects = genericDaoSupport.searchForList(hql, parameters);	
		
		return objects;
		//return genericDaoSupport.queryForList(sBuilder.toString(),warehouseId);
		//return genericDaoSupport.getJdbcTemplate().queryForList(sBuilder.toString(),warehouseId,DateUtil.parseDate(DateUtil.getPreviousDay(beginDate), DateUtil.DATETIME_FORMAT),DateUtil.parseDate(DateUtil.getNextDay(endDate), DateUtil.DATETIME_FORMAT));
	}

	
}
